﻿using System.Text;
using WyUnity.Data.DataAccess.Dialect;
using WyUnity.Data.TableMapper;
using WyUnity.Data.Query;
using WyUnity.Utility;
using WyUnity.Utility.ExtentionMethods;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace WyUnity.Data.DataAccess
{
    public partial class DbSession
    {
        #region 数据处理
        /// <summary>
        /// 插入一行数据
        /// </summary>
        public int Insert(ITable table)
        {
            return Insert(table.ToColumns(), table.GetTableName());
        }
        /// <summary>
        /// 更新一行数据（where默认为按主键）
        /// </summary>
        public int Update(ITable table, WhereClip where = null)
        {
            if (where == null) where = table.GetPrimaryKeyWhereClip();
            return Update(table.ToColumns(), table.GetTableName(), where);
        }
        public int UpdateField<T>(FieldBase field, object value, WhereClip where) where T : ITable, new()
        {
            var t = new T();
            var tablename = t.GetTableName();
            string sql = string.Format("UPDATE " + t.GetSQLClip(Dialect) + " SET "
                + string.Format(Dialect.NameFormat, field.FieldName)
                + "=@v "
                + where.ToSqlClip(Dialect));
            var pms = (where as IClip).Parameters.GetParams().ToList();
            var pv = Dialect.CreateParameter();
            pv.Value = value;
            pv.ParameterName = "@v";
            if (value != null)
            {
                pv.DbType = value.GetType().ToDbType();
            }
            else
            {
                pv.DbType = System.Data.DbType.String;
                pv.Value = System.DBNull.Value;
            }
            pms.Insert(0, pv);
            return ExecNoQuery(sql, pms.ToArray());
        }
        /// <summary>
        /// 删除一行数据
        /// </summary>
        public int Delete(ITable table)
        {
            return Delete(table.GetTableName(), table.GetPrimaryKeyWhereClip());
        }
        public int Delete<T>(WhereClip where = null) where T : IMapObject, new()
        {
            var temptable = new T();
            return Delete(temptable.GetSQLClip(Dialect), where);
        }

        /// <summary>
        /// 查询对象列表
        /// </summary>
        public List<T> QueryList<T>(WhereClip where = null, IOrderByClip order = null, int rowLimit = -1) where T : IMapObject, new()
        {
            var list = new List<T>();
            var temptable = new T();

            where = where.NullCheck(new WhereAll());
            string select;
            if (rowLimit <= 0)
                select = "SELECT * FROM ";
            else
                select = "SELECT TOP " + rowLimit + " * FROM ";

            using (var dr = QueryReader(select + temptable.GetSQLClip(Dialect)
                + " " + where.ToSqlClip(Dialect)
                + " " + order.NullCheck(OrderClip.None).ToSqlClip(),
                 (where as IClip).Parameters.GetParams()))
            {
                while (dr.Read())
                {
                    var item = new T();
                    item.LoadData(dr);
                    list.Add(item);
                }
                return list;
            }
        }


        /// <summary>
        /// 查询对象中的某属性的列表
        /// </summary>
        public List<F> QueryFieldList<T, F>(FieldBase field, WhereClip where = null, IOrderByClip order = null)
            where T : IMapObject, new()
        {
            var list = new List<F>();
            var temptable = new T();

            where = where.NullCheck(new WhereAll());
            using (var dr = QueryReader("SELECT [" + field.FieldName + "] FROM " + temptable.GetSQLClip(Dialect)
                + " " + where.ToSqlClip(Dialect)
                + " " + order.NullCheck(OrderClip.None).ToSqlClip(),
                 (where as IClip).Parameters.GetParams()))
            {
                while (dr.Read())
                {
                    list.Add(dr.GetValue(0).TryTo<F>());
                }
                return list;
            }
        }

        /// <summary>
        /// 查询对象列表(分页)
        /// </summary>
        public List<T> QueryListWithPage<T>(int startIndex, int limit, WhereClip where, OrderClip order) where T : IMapObject, new()
        {
            var temptable = new T();
            var rlt = new List<T>();

            using (var dr = QueryReaderWithPage("*",
                                temptable.GetSQLClip(Dialect),
                                where.NullCheck(new WhereAll()),
                                order.NullCheck(OrderClip.None),
                                startIndex,
                                limit))
            {
                while (dr.Read())
                {
                    var t = new T();
                    t.LoadData(dr);
                    rlt.Add(t);
                }
                return rlt;
            }
        }
        public List<T> QueryListWithPage<T>(int startIndex, int limit, OrderClip order) where T : IMapObject, new()
        {
            return QueryListWithPage<T>(startIndex, limit, new WhereAll(), order);
        }
        /// <summary>
        /// 查询第一个表对象
        /// 如果查询不到，则返回null
        /// </summary>
        public T QueryFirstOrDefault<T>(WhereClip where) where T : IMapObject, new()
        {
            var list = QueryList<T>(where, null, 1);
            if (list.Count == 0) return default(T);
            return list[0];
        }
        /// <summary>
        /// 查询唯一表对象
        /// 如果返回结果多于一行则发生MapException异常
        /// 如果查询不到，则返回null
        /// </summary>
        public T QuerySingle<T>(WhereClip where) where T : IMapObject, new()
        {
            var list = QueryList<T>(where);
            if (list.Count > 1) throw new MapException("QuerySingle方法查询到的对象不是一个");
            if (list.Count == 0) return default(T);
            return list[0];
        }
        /// <summary>
        /// 查询唯一表对象
        /// 如果返回结果多于一行则发生MapException异常
        /// 如果查询不到，则返回null
        /// </summary>
        public F QueryField<T, F>(FieldBase field, WhereClip where) where T : IMapObject, new()
        {
            var list = QueryFieldList<T, F>(field, where);
            if (list.Count > 1) throw new MapException("QuerySingle方法查询到的对象不是一个");
            if (list.Count == 0) return default(F);
            return list[0];
        }
        public int QueryRecordCount<T>(WhereClip where) where T : IMapObject, new()
        {
            var temptable = new T();
            return QuerySclare<int>("SELECT COUNT(1) FROM "
                + temptable.GetSQLClip(Dialect)
                + " " + where.ToSqlClip(Dialect),
                (where as IClip).Parameters.GetParams());
        }

        #endregion

        #region Scheme处理
        /// <summary>
        /// 表是否存在(tablename不能带限定符)
        /// </summary>
        public bool IsTableExists(string tableName)
        {
            return QueryRecordCount("sysobjects", new Where("name", tableName).And("type", "u")) == 1;
        }
        /// <summary>
        /// 表是否存在
        /// </summary>
        public bool IsExists<T>() where T : ITable, new()
        {
            var temptable = new T();
            return IsTableExists(temptable.GetTableName());
        }
        /// <summary>
        /// 删除一个表
        /// </summary>
        public void DropTable(string tableName)
        {
            if (IsTableExists(tableName))
            {
                ExecNoQuery("Drop Table " + string.Format(Dialect.NameFormat, tableName));
            }
        }
        /// <summary>
        /// 删除一个表
        /// </summary>
        public void DropTable<T>() where T : ITable, new()
        {
            var temptable = new T();

            DropTable(temptable.GetTableName());
        }
        /// <summary>
        /// 创建新表
        /// <param name="dropTableWhenExists">
        /// 如果为true,当表已经存在时，先删除原表
        /// 如果为false，当表存在时，跳过创建
        /// </param>
        /// </summary>
        public void CreateNewTable<T>(bool dropTableWhenExists) where T : ITable, new()
        {
            CreateTable(new T(), dropTableWhenExists);
        }

        /// <summary>
        /// 创建新表
        /// <param name="dropTableWhenExists">
        /// 如果为true,当表已经存在时，先删除原表
        /// 如果为false，当表存在时，跳过创建
        /// </param>
        /// </summary>
        public void CreateTable(ITable table, bool dropTableWhenExists)
        {
            if (IsTableExists(table.GetTableName()))
            {
                if (dropTableWhenExists)
                    DropTable(table.GetTableName());
                else
                    return;
            }

            var sql = new StringBuilder();
            var createPrimarykey = new StringBuilder();
            var cols = table.DefineColumns;

            sql.AppendLine("Create Table " + table.GetSQLClip(Dialect) + "(");

            sql.AppendLine(string.Join(",\n", cols.Aggregate(new List<string>()
                                                             , (list, c) =>
                                                                {
                                                                    list.Add(c.GetFieldClipOfCreateTable(Dialect));
                                                                    return list;
                                                                }).ToArray()));

            sql.AppendLine(")");

            Debug(sql.ToString());

            ExecNoQuery(sql.ToString());
        }

        #endregion
    }
}